﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

//using System.Data.OracleClient;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;

namespace RH.EBS.Core.Repository.DBUtility
{
    public class SqlBuilder
    {
        public static string connType="SQL";
        #region 解析DataSet数据，提取用来更新数据库的SqlItem信息。
        /// <summary>
        /// 提取更新DataSet指定表的Sql.指定排除某些列不做更新。
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static SqlItemArray BuildSqlItemArray(DataSet ds, string tableName, List<string> ExceptCols)
        {
            SqlItemArray myList = new SqlItemArray();
            List<SqlItem> tempList;

            DataTable dt = ds.Tables[tableName];

            tempList = buildDeleteSql(dt);
            if (tempList != null)
            {
                myList.SqlItemList.AddRange(tempList);
            }

            tempList = buildInsertSql(dt, ExceptCols);
            if (tempList != null)
            {
                myList.SqlItemList.AddRange(tempList);
            }

            tempList = buildUpdateSql(dt, ExceptCols);
            if (tempList != null)
            {
                myList.SqlItemList.AddRange(tempList);
            }

            //dt.DefaultView.RowStateFilter = DataViewRowState.CurrentRows;

            return myList;
        }

        /// <summary>
        /// 用TableName别名提取更新DataTable指定表的Sql.
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="tableName">别名</param>
        /// <returns></returns>
        public static SqlItemArray BuildSqlItemArray(DataTable dt, string tableName)
        {
            SqlItemArray myList = new SqlItemArray();
            List<SqlItem> tempList;

            ////存原来的名字
            //string tempName = dt.TableName;

            ////用别名替换原来的表名
            //dt.TableName = tableName;

            tempList = buildDeleteSql(dt, tableName);
            if (tempList != null)
            {
                myList.SqlItemList.AddRange(tempList);
            }

            tempList = buildInsertSql(dt, tableName);
            if (tempList != null)
            {
                myList.SqlItemList.AddRange(tempList);
            }

            tempList = buildUpdateSql(dt, tableName);
            if (tempList != null)
            {
                myList.SqlItemList.AddRange(tempList);
            }

            //dt.DefaultView.RowStateFilter = DataViewRowState.CurrentRows;

            ////还原表名
            //dt.TableName = tempName;

            return myList;
        }

        /// <summary>
        /// 提取更新DataSet指定表的Sql.
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static SqlItemArray BuildSqlItemArray(DataSet ds, string tableName)
        {
            SqlItemArray myList = new SqlItemArray();
            List<SqlItem> tempList;

            DataTable dt = ds.Tables[tableName];

            tempList = buildDeleteSql(dt);
            if (tempList != null)
            {
                myList.SqlItemList.AddRange(tempList);
            }

            tempList = buildInsertSql(dt, new List<string>());
            if (tempList != null)
            {
                myList.SqlItemList.AddRange(tempList);
            }

            tempList = buildUpdateSql(dt, new List<string>());
            if (tempList != null)
            {
                myList.SqlItemList.AddRange(tempList);
            }
            //dt.DefaultView.RowStateFilter = DataViewRowState.CurrentRows;

            return myList;
        }

        /// <summary>
        /// 提取更新DataSet内所有表的Sql.
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static SqlItemArray BuildSqlItemArray(DataSet ds)
        {
            SqlItemArray myList = new SqlItemArray();
            List<SqlItem> tempList;

            List<string> aryTableOrder = AdaptTableOrder(ds);

            for (int i = 0; i < aryTableOrder.Count; i++)
            {
                DataTable dt = ds.Tables[aryTableOrder[i]];

                tempList = buildDeleteSql(dt);
                if (tempList != null)
                {
                    myList.SqlItemList.AddRange(tempList);
                }

                tempList = buildUpdateSql(dt, new List<string>());
                if (tempList != null)
                {
                    myList.SqlItemList.AddRange(tempList);
                }
            }


            for (int i = aryTableOrder.Count - 1; i > -1; i--)
            {
                DataTable dt = ds.Tables[aryTableOrder[i]];

                tempList = buildInsertSql(dt, new List<string>());
                if (tempList != null)
                {
                    myList.SqlItemList.AddRange(tempList);
                }
                //dt.DefaultView.RowStateFilter = DataViewRowState.CurrentRows;
            }

            return myList;
        }

        //按更新顺序取出表名称集合
        public static List<string> AdaptTableOrder(DataSet ds)
        {
            List<string> aryParent = new List<string>();
            List<string> aryChild = new List<string>();
            List<string> aryParentCopy = new List<string>();

            List<string> aryTableOrder = new List<string>();

            foreach (DataRelation rel in ds.Relations)
            {
                aryParent.Add(rel.ParentTable.TableName);
                aryParentCopy.Add(rel.ParentTable.TableName);
                aryChild.Add(rel.ChildTable.TableName);
            }

            foreach (string parent in aryParentCopy)
            {
                List<string> tempList = GetChildNest(parent, aryParent, aryChild);

                foreach (string str in tempList)
                {
                    if (aryTableOrder.Contains(str) == false)
                    {
                        aryTableOrder.Add(str);
                    }
                }
            }

            //检查不在任何关系里的表
            foreach (DataTable dt in ds.Tables)
            {
                if (aryTableOrder.Contains(dt.TableName) == false)
                {
                    aryTableOrder.Add(dt.TableName);
                }
            }

            return aryTableOrder;
        }

        private static List<string> GetChildNest(string tblName, List<string> aryParent, List<string> aryChild)
        {
            List<string> aryReturn = new List<string>();

            // 不是父表，则直接添加到List
            if (aryParent.Contains(tblName) == false)
            {
                aryReturn.Add(tblName);
            }
            else
            {
                int i = aryParent.IndexOf(tblName);
                string tempChild = aryChild[i];

                aryParent.RemoveAt(i);
                aryChild.RemoveAt(i);

                List<string> tempList = GetChildNest(tempChild, aryParent, aryChild);
                for (int j = tempList.Count - 1; j > -1; j--)
                {
                    aryReturn.Add(tempList[j]);
                }
            }

            return aryReturn;
        }

        /// <summary>
        /// 以二进制格式提取更新DataSet的Sql信息。
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static byte[] BuildSqlItemArrayBytes(DataSet ds, string tableName, List<string> ExceptCols)
        {
            return GetSqlItemArrayBytes(BuildSqlItemArray(ds, tableName, ExceptCols));
        }

        /// <summary>
        /// 以二进制格式提取更新DataSet的Sql信息。
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static byte[] BuildSqlItemArrayBytes(DataSet ds, string tableName)
        {
            return GetSqlItemArrayBytes(BuildSqlItemArray(ds, tableName));
        }

        /// <summary>
        /// 以二进制格式提取更新DataSet的Sql信息。
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static byte[] BuildSqlItemArrayBytes(DataSet ds)
        {
            return GetSqlItemArrayBytes(BuildSqlItemArray(ds));
        }
        #endregion

        #region 解析SqlItem信息辅助函数(Private)
        private static List<SqlItem> buildInsertSql(DataTable dt, string tableName)
        {
            //dt.DefaultView.RowStateFilter = DataViewRowState.Added;
            DataView dv = new DataView(dt);
            dv.RowStateFilter = DataViewRowState.Added;

            if (dv.Count == 0)
            {
                return null;
            }

            List<SqlItem> myList = new List<SqlItem>();

            StringBuilder sb = new StringBuilder();

            string strSql = "";

            sb.Append("INSERT INTO ");
            sb.Append(tableName);
            sb.Append(" (");

            // 拼写列名
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (SqlCodeParser.IsReserveKey(dt.Columns[i].ColumnName))
                {
                    sb.Append("\"");
                    sb.Append(dt.Columns[i].ColumnName);
                    sb.Append("\"");
                }
                else
                {
                    sb.Append(dt.Columns[i].ColumnName);
                }
                if (i < dt.Columns.Count - 1)
                {
                    sb.Append(",");
                }
            }

            sb.Append(") VALUES (");

            // 拼写参数名
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (connType == "Odbc")
                {
                    sb.Append("?");
                }
                else 
                {
                    sb.Append("@P_");
                    sb.Append(dt.Columns[i].ColumnName);
                }                
                if (i < dt.Columns.Count - 1)
                {
                    sb.Append(",");
                }
            }

            sb.Append(")");

            strSql = sb.ToString();

            // 逐行遍历
            foreach (DataRowView dr in dv)
            {
                SqlItem mySqlItem = new SqlItem();

                // Sql完整串
                mySqlItem.StrSql = sb.ToString();

                // 添加参数信息
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    // 添加参数信息。
                    mySqlItem.AppendParameter("P_" + dt.Columns[i].ColumnName, dr[i], dt.Columns[i].DataType);
                }

                myList.Add(mySqlItem);
            }

            return myList;
        }

        private static List<SqlItem> buildUpdateSql(DataTable dt, string tableName)
        {
            //dt.DefaultView.RowStateFilter = DataViewRowState.ModifiedCurrent;

            DataView dv = new DataView(dt);

            dv.RowStateFilter = DataViewRowState.ModifiedCurrent;

            if (dv.Count == 0)
            {
                return null;
            }

            List<SqlItem> myList = new List<SqlItem>();

            // 逐行遍历
            foreach (DataRowView dr in dv)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("UPDATE ");
                sb.Append(tableName);
                sb.Append(" SET ");

                SqlItem mySqlItem = new SqlItem();

                // 逐列拼写Sql并加入参数信息。
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (sb.Length > 12 + tableName.Length)
                    {
                        sb.Append(",");
                    }
                    if (SqlCodeParser.IsReserveKey(dt.Columns[i].ColumnName))
                    {
                        sb.Append("\"");
                        sb.Append(dt.Columns[i].ColumnName);
                        sb.Append("\"");
                    }
                    else
                    {
                        sb.Append(dt.Columns[i].ColumnName);
                    }
                    if (connType == "Odbc")
                    {
                        sb.Append("=?");
                    }
                    else 
                    {
                        sb.Append("=@P_");
                        sb.Append(dt.Columns[i].ColumnName);
                    }
                    

                    // 添加参数信息。
                    mySqlItem.AppendParameter("P_" + dt.Columns[i].ColumnName, dr.Row[dt.Columns[i].ColumnName], dt.Columns[i].DataType);
                }

                sb.Append(" WHERE ");

                // 逐主键列拼写Where条件，并加入参数信息。
                for (int i = 0; i < dt.PrimaryKey.Length; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(" AND ");
                    }
                    if (SqlCodeParser.IsReserveKey(dt.PrimaryKey[i].ColumnName))
                    {
                        sb.Append("\"");
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                        sb.Append("\"");
                    }
                    else
                    {
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                    }
                    if (connType == "Odbc")
                    {
                        sb.Append("=?");
                    }
                    else 
                    {
                        sb.Append("=@KEY");
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                    }
                    // 添加参数信息。为Key字段的参数添加前缀"KEY"。如：WHERE P_ID=:KEYP_ID
                    mySqlItem.AppendParameter("KEY" + dt.PrimaryKey[i].ColumnName, dr.Row[dt.PrimaryKey[i].ColumnName, DataRowVersion.Original], dt.PrimaryKey[i].DataType);
                }

                // 完整的Sql
                mySqlItem.StrSql = sb.ToString();

                myList.Add(mySqlItem);
            }

            return myList;
        }

        private static List<SqlItem> buildDeleteSql(DataTable dt, string tableName)
        {
            //dt.DefaultView.RowStateFilter = DataViewRowState.Deleted;
            DataView dv = new DataView(dt);

            dv.RowStateFilter = DataViewRowState.Deleted;

            if (dv.Count == 0)
            {
                return null;
            }

            List<SqlItem> myList = new List<SqlItem>();

            // 逐行遍历
            foreach (DataRowView dr in dv)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("DELETE ");
                sb.Append(tableName);

                SqlItem mySqlItem = new SqlItem();

                sb.Append(" WHERE ");
                // 逐主键列拼写Where条件，并加入参数信息。
                for (int i = 0; i < dt.PrimaryKey.Length; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(" AND ");
                    }
                    if (SqlCodeParser.IsReserveKey(dt.PrimaryKey[i].ColumnName))
                    {
                        sb.Append("\"");
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                        sb.Append("\"");
                    }
                    else
                    {
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                    }
                    if (connType == "Odbc")
                    {
                        sb.Append("=?");
                    }
                    else 
                    {
                        sb.Append("=@KEY");
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                    }
                    // 添加参数信息。为Key字段的参数添加前缀"KEY"。如：WHERE P_ID=:KEYP_ID
                    mySqlItem.AppendParameter("KEY" + dt.PrimaryKey[i].ColumnName, dr.Row[dt.PrimaryKey[i].ColumnName, DataRowVersion.Original], dt.PrimaryKey[i].DataType);
                }

                // 完整的Sql
                mySqlItem.StrSql = sb.ToString();

                myList.Add(mySqlItem);
            }

            return myList;
        }

        private static List<SqlItem> buildInsertSql(DataTable dt, List<string> ExceptCols)
        {
            //dt.DefaultView.RowStateFilter = DataViewRowState.Added;

            DataView dv = new DataView(dt);

            dv.RowStateFilter = DataViewRowState.Added;


            if (dv.Count == 0)
            {
                return null;
            }

            List<SqlItem> myList = new List<SqlItem>();

            StringBuilder sb = new StringBuilder();

            string strSql = "";

            sb.Append("INSERT INTO ");
            sb.Append(dt.TableName);
            sb.Append(" (");

            if (ExceptCols != null)
            {
                for (int i = 0; i < ExceptCols.Count; i++)
                {
                    ExceptCols[i] = ExceptCols[i].ToLower();
                }
            }
            // 拼写列名
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                //只有当非排除的列才做更新
                if (ExceptCols == null || ExceptCols.Contains(dt.Columns[i].ColumnName.ToLower()) == false)
                {
                    if (SqlCodeParser.IsReserveKey(dt.Columns[i].ColumnName))
                    {
                        sb.Append("\"");
                        sb.Append(dt.Columns[i].ColumnName);
                        sb.Append("\"");
                    }
                    else
                    {
                        sb.Append(dt.Columns[i].ColumnName);
                    }

                    if (ExceptCols == null)
                    {
                        if (i < dt.Columns.Count - 1)
                        {
                            sb.Append(",");
                        }
                    }
                    else
                    {
                        //判断后面是否还有需要更新的列，如果有则添加",".
                        DataColumn tempCol = null;
                        for (int k = i + 1; k < dt.Columns.Count; k++)
                        {
                            if (ExceptCols.Contains(dt.Columns[k].ColumnName.ToLower()) == false)
                            {
                                tempCol = dt.Columns[k];
                                break;
                            }
                        }

                        if (tempCol != null)
                        {
                            sb.Append(",");
                        }
                    }
                }
            }

            sb.Append(") VALUES (");

            // 拼写参数名
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                //只有当非排除的列才做更新
                if (ExceptCols == null || ExceptCols.Contains(dt.Columns[i].ColumnName.ToLower()) == false)
                {
                    if (connType == "Odbc")
                    {
                        sb.Append("?");
                    }
                    else 
                    {
                        sb.Append("@P_");
                        sb.Append(dt.Columns[i].ColumnName);
                    }
                    if (ExceptCols == null)
                    {
                        if (i < dt.Columns.Count - 1)
                        {
                            sb.Append(",");
                        }
                    }
                    else
                    {
                        //判断后面是否还有需要更新的列，如果有则添加",".
                        DataColumn tempCol = null;
                        for (int k = i + 1; k < dt.Columns.Count; k++)
                        {
                            if (ExceptCols.Contains(dt.Columns[k].ColumnName.ToLower()) == false)
                            {
                                tempCol = dt.Columns[k];
                                break;
                            }
                        }

                        if (tempCol != null)
                        {
                            sb.Append(",");
                        }
                    }
                }
            }

            sb.Append(")");

            strSql = sb.ToString();

            // 逐行遍历
            foreach (DataRowView dr in dv)
            {
                SqlItem mySqlItem = new SqlItem();

                // Sql完整串
                mySqlItem.StrSql = sb.ToString();

                // 添加参数信息
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    //只有当非排除的列才做更新
                    if (ExceptCols == null || ExceptCols.Contains(dt.Columns[i].ColumnName.ToLower()) == false)
                    {
                        // 添加参数信息。
                        mySqlItem.AppendParameter("P_" + dt.Columns[i].ColumnName, dr[i], dt.Columns[i].DataType);
                    }
                }

                myList.Add(mySqlItem);
            }

            return myList;
        }

        private static List<SqlItem> buildUpdateSql(DataTable dt, List<string> ExceptCols)
        {
            //dt.DefaultView.RowStateFilter = DataViewRowState.ModifiedCurrent;

            DataView dv = new DataView(dt);

            dv.RowStateFilter = DataViewRowState.ModifiedCurrent;

            if (dv.Count == 0)
            {
                return null;
            }
            if (ExceptCols != null)
            {
                for (int i = 0; i < ExceptCols.Count; i++)
                {
                    ExceptCols[i] = ExceptCols[i].ToLower();
                }
            }
            List<SqlItem> myList = new List<SqlItem>();

            // 逐行遍历
            foreach (DataRowView dr in dv)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("UPDATE ");
                sb.Append(dt.TableName);
                sb.Append(" SET ");

                SqlItem mySqlItem = new SqlItem();

                // 逐列拼写Sql并加入参数信息。
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    //只有当非排除的列才做更新
                    if (ExceptCols == null || ExceptCols.Contains(dt.Columns[i].ColumnName.ToLower()) == false)
                    {
                        if (sb.Length > 12 + dt.TableName.Length)
                        {
                            sb.Append(",");
                        }
                        if (SqlCodeParser.IsReserveKey(dt.Columns[i].ColumnName))
                        {
                            sb.Append("\"");
                            sb.Append(dt.Columns[i].ColumnName);
                            sb.Append("\"");
                        }
                        else
                        {
                            sb.Append(dt.Columns[i].ColumnName);
                        }
                        if (connType == "Odbc")
                        {
                            sb.Append("=?");
                        }
                        else 
                        {
                            sb.Append("=@P_");
                            sb.Append(dt.Columns[i].ColumnName);
                        }
                        

                        //if (i < dt.Columns.Count - 1)
                        //{
                        //sb.Append(",");
                        // }
                        // 添加参数信息。
                        mySqlItem.AppendParameter("P_" + dt.Columns[i].ColumnName, dr[dt.Columns[i].ColumnName], dt.Columns[i].DataType);
                    }
                }

                sb.Append(" WHERE ");

                // 逐主键列拼写Where条件，并加入参数信息。
                for (int i = 0; i < dt.PrimaryKey.Length; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(" AND ");
                    }
                    if (SqlCodeParser.IsReserveKey(dt.PrimaryKey[i].ColumnName))
                    {
                        sb.Append("\"");
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                        sb.Append("\"");
                    }
                    else
                    {
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                    }
                    if (connType == "Odbc")
                    {
                        sb.Append("=?");
                    }
                    else 
                    {
                        sb.Append("=@KEY");
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                    }

                    // 添加参数信息。为Key字段的参数添加前缀"KEY"。如：WHERE P_ID=:KEYP_ID
                    mySqlItem.AppendParameter("KEY" + dt.PrimaryKey[i].ColumnName, dr.Row[dt.PrimaryKey[i].ColumnName, DataRowVersion.Original], dt.PrimaryKey[i].DataType);
                }

                // 完整的Sql
                mySqlItem.StrSql = sb.ToString();

                myList.Add(mySqlItem);
            }

            return myList;
        }

        private static List<SqlItem> buildDeleteSql(DataTable dt)
        {
            //dt.DefaultView.RowStateFilter = DataViewRowState.Deleted;

            DataView dv = new DataView(dt);

            dv.RowStateFilter = DataViewRowState.Deleted;

            if (dv.Count == 0)
            {
                return null;
            }

            List<SqlItem> myList = new List<SqlItem>();

            // 逐行遍历
            foreach (DataRowView dr in dv)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("DELETE ");
                sb.Append(dt.TableName);

                SqlItem mySqlItem = new SqlItem();

                sb.Append(" WHERE ");
                // 逐主键列拼写Where条件，并加入参数信息。
                for (int i = 0; i < dt.PrimaryKey.Length; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(" AND ");
                    }
                    if (SqlCodeParser.IsReserveKey(dt.PrimaryKey[i].ColumnName))
                    {
                        sb.Append("\"");
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                        sb.Append("\"");
                    }
                    else
                    {
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                    }
                    if (connType == "Odbc")
                    {
                        sb.Append("=?");
                    }
                    else 
                    {
                        sb.Append("=@KEY");
                        sb.Append(dt.PrimaryKey[i].ColumnName);
                    }
                    // 添加参数信息。为Key字段的参数添加前缀"KEY"。如：WHERE P_ID=:KEYP_ID
                    mySqlItem.AppendParameter("KEY" + dt.PrimaryKey[i].ColumnName, dr.Row[dt.PrimaryKey[i].ColumnName, DataRowVersion.Original], dt.PrimaryKey[i].DataType);
                }

                // 完整的Sql
                mySqlItem.StrSql = sb.ToString();

                myList.Add(mySqlItem);
            }

            return myList;
        }
        #endregion

        #region 二进制与对象转换
        // 得到SqlItemArray对象的二进制数据。
        public static byte[] GetSqlItemArrayBytes(SqlItemArray obj)
        {
            IFormatter formatter = new BinaryFormatter();
            Stream stream = new MemoryStream();
            formatter.Serialize(stream, obj);

            byte[] myStream = ((MemoryStream)stream).ToArray();
            stream.Close();
            return myStream;
        }

        // 从二进制流恢复SqlItemArray对象。
        public static SqlItemArray DeserializeSqlItemArray(byte[] myStream)
        {
            IFormatter formatter = new BinaryFormatter();
            Stream stream = new MemoryStream(myStream);
            SqlItemArray obj = (SqlItemArray)formatter.Deserialize(stream);
            stream.Close();
            return obj;
        }

        // 得到SqlItem对象的二进制数据。
        public static byte[] GetSqlItemBytes(SqlItem obj)
        {
            IFormatter formatter = new BinaryFormatter();
            Stream stream = new MemoryStream();
            formatter.Serialize(stream, obj);

            byte[] myStream = ((MemoryStream)stream).ToArray();
            stream.Close();
            return myStream;
        }

        // 从二进制流恢复SqlItem对象。
        public static SqlItem DeserializeSqlItem(byte[] myStream)
        {
            IFormatter formatter = new BinaryFormatter();
            Stream stream = new MemoryStream(myStream);
            SqlItem obj = (SqlItem)formatter.Deserialize(stream);
            stream.Close();
            return obj;
        }
        #endregion


        #region 提取更新内容的日志
        //提取更新内容的日志
        public static List<DBLogItem> BuildDBLogItemForDataSet(DataSet ds, string tableName)
        {
            DataTable dt = ds.Tables[tableName];
            List<DBLogItem> tempList;
            List<DBLogItem> myList = new List<DBLogItem>();

            tempList = buildDeletLog(dt);
            if (tempList != null)
            {
                myList.AddRange(tempList);
            }

            tempList = buildInsertLog(dt);
            if (tempList != null)
            {
                myList.AddRange(tempList);
            }

            tempList = buildUpdateLog(dt);
            if (tempList != null)
            {
                myList.AddRange(tempList);
            }

            //dt.DefaultView.RowStateFilter = DataViewRowState.CurrentRows;

            return myList;
        }

        private static List<DBLogItem> buildUpdateLog(DataTable dt)
        {
            //dt.DefaultView.RowStateFilter = DataViewRowState.ModifiedCurrent;

            DataView dv = new DataView(dt);
            dv.RowStateFilter = DataViewRowState.ModifiedCurrent;

            if (dv.Count == 0)
            {
                return null;
            }

            List<DBLogItem> myList = new List<DBLogItem>();

            // 逐行遍历
            foreach (DataRowView dr in dv)
            {
                DBLogItem logItem = new DBLogItem();

                StringBuilder sb = new StringBuilder();

                // 逐主键列拼写Key条件，并加入参数信息。
                for (int i = 0; i < dt.PrimaryKey.Length; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(" , ");
                    }
                    sb.Append(dt.PrimaryKey[i].ColumnName);
                    sb.Append(":");
                    sb.Append(dr.Row[dt.PrimaryKey[i].ColumnName, DataRowVersion.Original].ToString());
                }

                logItem.Keys = sb.ToString();

                sb = new StringBuilder();
                // 逐列拼写Sql并加入参数信息。
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (DataHasChanged(dr.Row, dt.Columns[i].ColumnName) == true)
                    {
                        if (sb.Length > 0)
                        {
                            sb.Append(",");
                        }

                        sb.Append(dt.Columns[i].ColumnName);
                        sb.Append(":");
                        if (Convert.IsDBNull(dr.Row[dt.Columns[i].ColumnName, DataRowVersion.Original]))
                        {
                            sb.Append("DBNULL");
                        }
                        else
                        {
                            sb.Append(dr.Row[dt.Columns[i].ColumnName, DataRowVersion.Original].ToString());
                        }
                        sb.Append("->");
                        if (Convert.IsDBNull(dr[dt.Columns[i].ColumnName]))
                        {
                            sb.Append("DBNULL");
                        }
                        else
                        {
                            sb.Append(dr[dt.Columns[i].ColumnName].ToString());
                        }
                    }
                }
                logItem.Log = sb.ToString();

                logItem.ModifyFlag = "U";

                myList.Add(logItem);
            }

            return myList;
        }

        private static List<DBLogItem> buildInsertLog(DataTable dt)
        {
            //dt.DefaultView.RowStateFilter = DataViewRowState.Added;

            DataView dv = new DataView(dt);
            dv.RowStateFilter = DataViewRowState.Added;


            if (dv.Count == 0)
            {
                return null;
            }

            List<DBLogItem> myList = new List<DBLogItem>();

            // 逐行遍历
            foreach (DataRowView dr in dv)
            {
                DBLogItem logItem = new DBLogItem();

                StringBuilder sb = new StringBuilder();

                // 逐主键列拼写Key条件，并加入参数信息。
                for (int i = 0; i < dt.PrimaryKey.Length; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(" , ");
                    }
                    sb.Append(dt.PrimaryKey[i].ColumnName);
                    sb.Append(":");
                    sb.Append(dr.Row[dt.PrimaryKey[i].ColumnName].ToString());
                }

                logItem.Keys = sb.ToString();

                sb = new StringBuilder();
                // 逐列拼写Sql并加入参数信息。
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (Convert.IsDBNull(dr[dt.Columns[i].ColumnName]) == false)
                    {
                        if (sb.Length > 0)
                        {
                            sb.Append(",");
                        }

                        sb.Append(dt.Columns[i].ColumnName);
                        sb.Append(":");
                        sb.Append(dr[dt.Columns[i].ColumnName].ToString());
                    }
                }
                logItem.Log = sb.ToString();

                logItem.ModifyFlag = "A";

                myList.Add(logItem);
            }

            return myList;
        }

        private static List<DBLogItem> buildDeletLog(DataTable dt)
        {
            //dt.DefaultView.RowStateFilter = DataViewRowState.Deleted;

            DataView dv = new DataView(dt);
            dv.RowStateFilter = DataViewRowState.Deleted;

            if (dv.Count == 0)
            {
                return null;
            }

            List<DBLogItem> myList = new List<DBLogItem>();

            // 逐行遍历
            foreach (DataRowView dr in dv)
            {
                DBLogItem logItem = new DBLogItem();

                StringBuilder sb = new StringBuilder();
                // 逐主键列拼写Key条件，并加入参数信息。
                for (int i = 0; i < dt.PrimaryKey.Length; i++)
                {
                    if (i > 0)
                    {
                        sb.Append(" , ");
                    }
                    sb.Append(dt.PrimaryKey[i].ColumnName);
                    sb.Append(":");
                    sb.Append(dr.Row[dt.PrimaryKey[i].ColumnName, DataRowVersion.Original].ToString());
                }

                logItem.Keys = sb.ToString();

                sb = new StringBuilder();
                // 逐列拼写Sql并加入参数信息。
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (Convert.IsDBNull(dr[dt.Columns[i].ColumnName]) == false)
                    {
                        if (sb.Length > 0)
                        {
                            sb.Append(",");
                        }

                        sb.Append(dt.Columns[i].ColumnName);
                        sb.Append(":");
                        sb.Append(dr[dt.Columns[i].ColumnName].ToString());
                    }
                }
                logItem.Log = sb.ToString();

                logItem.ModifyFlag = "D";

                myList.Add(logItem);
            }

            return myList;
        }
        #endregion

        //用于判断某一列的值是否改变
        private static bool DataHasChanged(DataRow dr, string columnName)
        {
            if (dr.HasVersion(DataRowVersion.Original) == false)
                return false;

            if (Convert.IsDBNull(dr[columnName]) == true && Convert.IsDBNull(dr[columnName, DataRowVersion.Original]) == true)
            {
                return false;
            }
            else
                if (Convert.IsDBNull(dr[columnName]) == true && Convert.IsDBNull(dr[columnName, DataRowVersion.Original]) == false)
                {
                    return true;
                }
                else
                    if (Convert.IsDBNull(dr[columnName]) == false && Convert.IsDBNull(dr[columnName, DataRowVersion.Original]) == true)
                    {
                        return true;
                    }
                    else
                        if (dr[columnName].ToString() != dr[columnName, DataRowVersion.Original].ToString())
                        {
                            return true;
                        }

            return false;
        }


        public static byte[] DataSetToBytes(DataSet ds)
        {
            byte[] bArrayResult = null;
            try
            {
                ds.RemotingFormat = SerializationFormat.Binary;
                MemoryStream ms = new MemoryStream();
                IFormatter bf = new BinaryFormatter();
                bf.Serialize(ms, ds);
                bArrayResult = ms.ToArray();
                ms.Close();
                // 
            }
            catch (Exception ee)
            {
            }
            return bArrayResult;
        }

        public static DataSet BytesToDataSet(byte[] bytes)
        {
            // 反序列化的过程
            MemoryStream ms = new MemoryStream(bytes);
            IFormatter bf = new BinaryFormatter();
            object obj = bf.Deserialize(ms);
            DataSet dsResult = (DataSet)obj;
            //
            ms.Close();
            return dsResult;
        }
    }
}
